﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Microsoft.ApplicationBlocks.Data;

namespace DBA.DataBaseAccess
{
    public class DBAccess
    {
        private static DBConfig DBC = new DBConfig();
        public static void SetDBConfig()
        {
            DBC = new DBConfig();
        }
        public static void SetDBConfig(string serverip, SQLServerVer ver, string dbname, string userid, string pwd)
        {
            DBC = new DBConfig(serverip, ver, dbname, userid, pwd);
        }
        public static void SetDBConfig(string serverip, SQLServerVer ver)
        {
            DBC = new DBConfig(serverip, ver);
        }
        public static void SetDBConfig(string serverip, SQLServerVer ver, string dbfilename)
        {
            DBC = new DBConfig(serverip, ver, dbfilename);
        }
        #region UserIndentify
        public static bool UserIndentify(string user_name, string user_password)
        {
            string sp = "Select_user_infos_Dynamic";
            string wherecondition = "user_name = '" + user_name +
                "' and user_password = '" + user_password + "'";
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure, sp,
                new SqlParameter("@WhereCondition", wherecondition));
            return ds.Tables[0].Rows.Count == 1;
        }

        public static bool UserIndentify(string user_name, string user_password, string user_type_id)
        {
            string sp = "Select_user_infos_Dynamic";
            string wherecondition = "user_name = '" + user_name +
                "' and user_password = '" + user_password + 
                "' and user_type_id = " + user_type_id;
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure, sp,
                new SqlParameter("@WhereCondition", wherecondition));
            return ds.Tables[0].Rows.Count == 1;
        }

        public static bool UserIndentifyByDept(string user_name, string user_password, string department_id)
        {
            string wherecondition = "user_name = '" + user_name +
                "' and user_password = '" + user_password +
                "' and department_id = " + department_id;
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", wherecondition));
            return ds.Tables[0].Rows.Count == 1;
        }

        public static bool UserIndentifyBySN(string department_sn, string user_password)
        {
            string sp = "Select_View_user_indentify_sn";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@department_sn", department_sn);
            param[1] = new SqlParameter("@user_password", user_password);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            return ds.Tables[0].Rows.Count == 1;
        }

        public static bool UserIndentifyByDeptRealName(string department_sn, string user_realname)
        {
            string sp = "Select_View_user_indentify_dept_realname";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@department_sn", department_sn);
            param[1] = new SqlParameter("@user_realname", user_realname);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            return ds.Tables[0].Rows.Count == 1;
        }

        public static string GetUserIdBySNRealName(string department_sn, string user_realname)
        {
            string sp = "Select_View_user_indentify_dept_realname";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@department_sn", department_sn);
            param[1] = new SqlParameter("@user_realname", user_realname);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_id"].ToString();
            else
                return null;
        }

        public static string GetUserIdBySN(string department_sn, string user_password)
        {
            string sp = "Select_View_user_indentify_sn";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@department_sn", department_sn);
            param[1] = new SqlParameter("@user_password", user_password);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_id"].ToString();
            else
                return null;
        }

        public static string GetDepartmentIdByUserId(string user_id)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", "user_id =" + user_id ));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["department_id"].ToString().Trim();
            else
                return null;
        }

        public static string GetDepartmentNameByUserId(string user_id)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic", 
                new SqlParameter("@WhereCondition", "user_id='" + user_id +"'"));
            if (ds.Tables[0].Rows.Count != 1)
                return null;
            string department_id = ds.Tables[0].Rows[0]["department_id"].ToString().Trim();
            ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_department_info",
                new SqlParameter("@department_id", department_id));
            return ds.Tables[0].Rows[0]["department_name"].ToString().Trim();
        }

        public static string GetDepartmentPNameByUserId(string user_id)
        {
            string department_id = GetDepartmentIdByUserId(user_id);
            if (department_id == null)
                return null;
            
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_department_info",
                new SqlParameter("@department_id", department_id));
            if (ds.Tables[0].Rows.Count != 1)
                return null;
            string department_parent_id = ds.Tables[0].Rows[0]["department_parent_id"].ToString().Trim();
            ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_department_info",
                new SqlParameter("@department_id", department_parent_id));
            return ds.Tables[0].Rows[0]["department_name"].ToString().Trim();
        }

        public static string GetUserIdByUserName(string user_name)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", "user_name = '" + user_name + "'"));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_id"].ToString().Trim();
            else
                return null;
        }

        public static string GetUserIdByRealName(string user_realname)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", "user_realname = '" + user_realname + "'"));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_id"].ToString().Trim();
            else
                return null;
        }

        public static string GetUserNameByUserId(string user_id)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", "user_id = " + user_id));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_name"].ToString().Trim();
            else
                return null;
        }

        public static string GetUserRealNameByUserId(string user_id)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_user_infos_Dynamic",
                new SqlParameter("@WhereCondition", "user_id = " + user_id));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["user_realname"].ToString().Trim();
            else
                return null;
        }


        #endregion

        #region BudgetReport
        public static string GetBudgetTypeIdByBudgetTypeName(string budget_type_name)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                "Select_budget_type_infos_Dynamic",
                new SqlParameter("@WhereCondition", "budget_type_name='" + budget_type_name + "'"));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["budget_type_id"].ToString().Trim();
            else
                return null;
        }

        public static bool InsertBudgetData(string budget_range_id, string budget_name,
            string budget_price, string budget_balance, 
            string pay_method_id, string purchase_method_id, 
            string budget_type_id, string budget_remark, 
            string user_id, string[,] budget_item_info,
            string budget_approve_needcount)
        {
            string budget_id;
            SqlParameter[] param = new SqlParameter[15];
            param[0] = new SqlParameter("@budget_id", null);
            param[1] = new SqlParameter("@budget_range_id", budget_range_id);/////
            param[2] = new SqlParameter("@pay_method_id", pay_method_id);
            param[3] = new SqlParameter("@budget_price", budget_price);
            param[4] = new SqlParameter("@budget_balance", budget_balance);
            param[5] = new SqlParameter("@budget_remark", budget_remark);
            param[6] = new SqlParameter("@budget_type_id", budget_type_id);
            param[7] = new SqlParameter("@budget_state_id", "0");
            param[8] = new SqlParameter("@user_id", user_id);
            param[9] = new SqlParameter("@budget_report_datetime", DateTime.Now);
            param[10] = new SqlParameter("@budget_approve_datetime", null);
            param[11] = new SqlParameter("@budget_approve_needcount", budget_approve_needcount);
            param[12] = new SqlParameter("@purchase_method_id", purchase_method_id);
            param[13] = new SqlParameter("@is_allapproved", false);
            param[14] = new SqlParameter("@budget_name", budget_name);
            
            try
            {
                
                budget_id = SqlHelper.ExecuteScalar(DBC.GetConnString(),
                    CommandType.StoredProcedure,
                    "Insert_budget_info",
                    param).ToString();
            }
            catch(Exception ex)
            {
                return false;
            }

            try
            {
                DBAccess.InsertBudgetItemInfo(budget_id, budget_item_info);
            }
            catch (Exception ex)
            {
                return false;
            }

            return true;
        }

        public static bool InsertBudgetItemInfo(string budget_id, string[,] budget_item_info)
        {
            string sp = "Insert_budget_item_info";
            for (int i = 0; i < budget_item_info.GetLongLength(0); i++)
            {
                SqlParameter[] param = new SqlParameter[7];
                param[0] = new SqlParameter("@budget_id", budget_id);
                param[1] = new SqlParameter("@budget_item_name", budget_item_info[i,0]);
                param[2] = new SqlParameter("@budget_item_count", budget_item_info[i, 1]);
                param[3] = new SqlParameter("@budget_item_unit", budget_item_info[i, 2]);
                param[4] = new SqlParameter("@budget_item_price", budget_item_info[i, 3]);
                param[5] = new SqlParameter("@budget_item_remark", null);
                param[6] = new SqlParameter("@budget_item_info_id", null);
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure, sp, param);
            }
            return true;
        }

        public static DataSet GetBudgetTypeNoParent()
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@WhereCondition", "budget_type_parentid IS NULL");
            return SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure,
                "Select_budget_type_infos_Dynamic",
                param);
        }

        public static DataSet GetPayMethod()
        {
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure, 
                "Select_pay_method_infos_All");
        }

        public static DataSet GetPurchaseMethod()
        {
            return SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure,
                "Select_purchase_method_infos_All");
        }

        public static DataSet GetBudgetRange()
        {
            return SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure,
                "Select_budget_range_infos_All");
        }
        public static DataSet GetAllDeptPName()
        {
            string sp = "Select_department_infos_Dynamic";
            string wherecondition = "department_level = 2";
            
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure, 
                sp, new SqlParameter("@WhereCondition", wherecondition));
        }
        public static DataSet GetDeptNameByPId(string department_pid)
        {
            string sp = "Select_department_infos_Dynamic";
            string wherecondition = "department_parent_id = " + department_pid;
            
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure, 
                sp, new SqlParameter("@WhereCondition", wherecondition));

        }
        public static DataSet GetAllBudgetState()
        {
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure, 
                "Select_budget_state_infos_All");
        }

        public static string GetPayMethodIdByName(string pay_method_name)
        {
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(),
                   CommandType.StoredProcedure,
                   "Select_pay_method_infos_Dynamic",
                   new SqlParameter("@WhereCondition", "pay_method_name ='"+ pay_method_name +"'"));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["pay_method_id"].ToString().Trim();
            else
                return null;
        }
        public static DataSet GetBudgetTypeByParentId(string budget_type_parentid)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@budget_type_parentid", budget_type_parentid);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(),
                CommandType.StoredProcedure,
                "Select_budget_type_infos_By_budget_type_parentid",
                param);
        }
        #endregion

        #region BudgetCheck

        public static DataSet GetBudgetInfo(string budget_range_id, string budget_type_id, string department_id,
            string pay_method_id, string purchase_method_id,
            string budget_state_id, int is_allapproved, int is_fullinfo, int budget_have_balance)
        {
            string wherecondition = "1 = 1", sp;

            if (budget_type_id != null)
            {
                wherecondition += " and (budget_type_id = " + budget_type_id;
                wherecondition += " or budget_type_pid = " + budget_type_id + ")";
            }
            if (department_id != null)
            {
                wherecondition += " and (department_id = " + department_id;
                wherecondition += " or department_pid = " + department_id + ")";
            }
            if (budget_range_id != null)
                wherecondition += " and budget_range_id = " + budget_range_id;
            if (pay_method_id != null)
                wherecondition += " and pay_method_id = " + pay_method_id;
            if (purchase_method_id != null)
                wherecondition += " and purchase_method_id = " + purchase_method_id;
            if (budget_state_id != null)
                wherecondition += " and budget_state_id = " + budget_state_id;
            if (is_allapproved != -1)
            {
                if (is_allapproved == 1)
                    wherecondition += " and is_allapproved = 1";
                else
                    wherecondition += " and is_allapproved = 0";
            }
            if (budget_have_balance != -1)
            {
                if (budget_have_balance == 1)
                    wherecondition += " and budget_balance > 0";
                else
                    wherecondition += " and budget_balance = 0";
            }
            if (is_fullinfo == 1)
            {
                sp = "Select_View_budget_fullinfo__Dynamic";
            }
            else
            {
                sp = "Select_View_budget_priceinfo__Dynamic";
            }
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@WhereCondition", wherecondition));
        }

        public static DataSet GetBudgetInfo(string budget_range_id, string budget_type_id, string department_id, 
            string pay_method_id, string purchase_method_id, 
            string budget_state_id, bool is_allapproved, bool is_fullinfo)
        {
            return GetBudgetInfo(budget_range_id, budget_type_id, department_id,
                pay_method_id, purchase_method_id, budget_state_id,
                (is_allapproved ? 1 : 0), (is_fullinfo ? 1 : 0), 1);
        }
        public static DataSet GetBudgetInfo(string budget_range_id, string budget_type_id, string department_id,
            string pay_method_id, string purchase_method_id, 
            string budget_state_id, bool is_fullinfo)
        {
            return GetBudgetInfo(budget_range_id, budget_type_id, department_id,
                  pay_method_id, purchase_method_id, budget_state_id,
                  -1, (is_fullinfo ? 1 : 0), 1);
        }

        public static DataSet GetBudgetFullInfoById(string budget_id)
        {
            string wherecondition = "budget_id = " + budget_id;
            string sp = "Select_View_budget_fullinfo__Dynamic";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@WhereCondition", wherecondition));
        }
        public static DataSet GetBudgetInfoByStateId(string budget_state_id)
        {
            string sp = "Select_View_budget_fullinfo_by_budget_state_id";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@budget_state_id", budget_state_id));
        }

        public static DataSet GetBudgetInfoByStateDepartment(string budget_state_id, string department_id)
        {
            return GetBudgetInfo(null, null, department_id, null, null, budget_state_id, true);
        }

        public static DataSet GetAllBudgetInfo()
        {
            string sp = "Select_budget_infos_All";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp);
        }

        public static string GetBudgetStateIdByName(string budget_type_name)
        {
            string sp = "Select_budget_type_infos_Dynamic";
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@WhereCondition", "budget_type_name = '"+ budget_type_name +"'"));
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["budget_type_id"].ToString().Trim();
            else
                return null;
        }

        public static DataSet GetBudgetItemInfo(string budget_id)
        {
            string sp = "Select_View_budget_item_fullinfo_by_budget_id";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@budget_id", budget_id));
        }

        public static DataSet GetApproveFullLog(string budget_id)
        {
            string sp = "Select_budget_item_infos_By_budget_id";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@budget_id", budget_id));
        }


        public static DataSet GetDeptBudgetType(string department_id)
        {
            string sp = "Select_dept_budget_type_matches_By_department_id";
            SqlParameter param = new SqlParameter("@department_id", department_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }
        #endregion

        #region BudgetApprove

        public static bool UserApproveAuthorityEqual(string budget_id, string user_id)
        {
            string sp = "Select_View_authority_equal__Dynamic";
            string wherecondition = "budget_id = " + budget_id + 
                " and user_id = "+ user_id;
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@WhereCondition", wherecondition));
            return ds.Tables[0].Rows.Count == 1;
        }

        public static bool IsBudgetBelongUsersDept(string budget_id, string user_id)
        {
            string department_id = DBAccess.GetDepartmentIdByUserId(user_id);
            DataSet ds = DBAccess.GetBudgetFullInfoById(budget_id);
            if (ds.Tables[0].Rows[0]["department_id"].ToString() == department_id ||
                ds.Tables[0].Rows[0]["department_pid"].ToString() == department_id)
                return true;
            else
                return false;
        }

        public static string GetBudgetStateIdByBudgetId(string budget_id)
        {
            string sp = "Select_budget_info";
            SqlParameter parma = new SqlParameter("@budget_id", budget_id);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure,
                sp, parma);
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["budget_state_id"].ToString().Trim();
            else
                return null;

        }

        public static string GetNextApprovedStateId(string budget_id, bool isapproved)
        {
            string sp = "Select_budget_info";
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@budget_id", budget_id));
            string budget_state_id = ds.Tables[0].Rows[0]["budget_state_id"].ToString();

            sp = "Select_budget_state_info";
            SqlParameter param = new SqlParameter("budget_state_id", budget_state_id);
            ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), 
                CommandType.StoredProcedure,
                sp, param);
            if(isapproved)
                return ds.Tables[0].Rows[0]["approve_budget_state_id"].ToString();
            else
                return ds.Tables[0].Rows[0]["reject_budget_state_id"].ToString();
            return null;
        }

        public static void ChangeBudgetStateId(string budget_id, string budget_state_id)
        {
            string sp = "Update_budget_info_state";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@budget_id", budget_id);
            param[1] = new SqlParameter("@budget_state_id", budget_state_id);
            param[2] = new SqlParameter("@budget_approve_datetime", DateTime.Now);
            SqlHelper.ExecuteNonQuery(DBC.GetConnString(), 
                CommandType.StoredProcedure,
                sp, param);
        }

        public static bool IsBudgetAllApproved(string budget_id)
        {
            string sp = "Select_View_budget_fullinfo__Dynamic";
            string wherecondition = "budget_id = " + budget_id +
                "and budget_approve_needcount = budget_approve_count";
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure, 
                sp, new SqlParameter("@WhereCondition", wherecondition));
            return ds.Tables[0].Rows.Count == 1;
        }

        public static void ChangeIsAllApproved(string budget_id, bool is_allapproved)
        {
            string sp = "Update_budget_info_is_allapproved";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@budget_id", budget_id);
            param[1] = new SqlParameter("@is_allapproved", is_allapproved);
            SqlHelper.ExecuteNonQuery(DBC.GetConnString(),
                CommandType.StoredProcedure,
                sp, param);
        }

        public static void ChangeBudgetApproveNeedCount(string budget_id, string budget_approve_needcount)
        {
            string sp = "Update_budget_info_approve_needcount";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@budget_approve_needcount", budget_approve_needcount);
            param[1] = new SqlParameter("@budget_id", budget_id);
            SqlHelper.ExecuteNonQuery(DBC.GetConnString(),
                CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetBudgetApproveNeedCountInfo()
        {
            string sp = "Select_budget_approve_needcount_infos_All";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure, sp);
        }

        public static string GetBudgetStateToApprove(string user_id)
        {
            string sp = "Select_View_user_fullinfo_by_user_id";
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, new SqlParameter("@user_id", user_id));
            if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["budget_state_id"].ToString();
            else
                return null;
        }

        public static void InsertApproveLog(string budget_id, 
            string budget_state_id_before, string budget_state_id_after,
            string user_id, string approve_remark, bool is_approved, bool is_fapprove)
        {
            string sp = "Insert_approve_log";
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@budget_id", budget_id);
            param[1] = new SqlParameter("@budget_state_id_before", budget_state_id_before);
            param[2] = new SqlParameter("@budget_state_id_after", budget_state_id_after);
            param[3] = new SqlParameter("@user_id", user_id);
            param[4] = new SqlParameter("@approve_remark", approve_remark);
            param[5] = new SqlParameter("@is_approved", (is_approved));
            param[6] = new SqlParameter("@is_fapprove", (is_fapprove));
            param[7] = new SqlParameter("@approve_id", null);
            param[8] = new SqlParameter("@approve_datetime", DateTime.Now);

            SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure, sp, param);
        }


        #endregion

        #region BudgetPay

       
        public static DataSet GetBudgetItemFullInfo(string budget_type_id, string department_id,
            string budget_range_id, bool is_allapproved)
        {
            string sp = "Select_View_budget_item_fullinfo";
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@budget_type_id", budget_type_id);
            param[1] = new SqlParameter("@department_id", department_id);
            param[2] = new SqlParameter("@budget_range_id", budget_range_id);
            param[3] = new SqlParameter("@is_allapproved", is_allapproved);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetBorrowBudgetItemFullInfo(string user_id, bool is_allpayback)
        {
            string sp = "Select_View_borrow_item_fullinfo_by_user_id";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@user_id", user_id);
            param[1] = new SqlParameter("@is_allpayback", is_allpayback);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure, 
                sp, param);
        }

        public static DataSet GetBorrowBudgetItemFullInfo(string budget_type_id, string department_id,
            string budget_range_id, bool is_allapproved)
        {
            string sp = "Select_View_budget_item_fullinfo";
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@budget_type_id", budget_type_id);
            param[1] = new SqlParameter("@department_id", department_id);
            param[2] = new SqlParameter("@budget_range_id", budget_range_id);
            param[3] = new SqlParameter("@is_allapproved", is_allapproved);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static bool ChangeBorrowPayPriceSub(string borrow_id, string pay_price_sub)
        {
            string sp = "Update_borrow_pay_price_sub";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@borrow_id", borrow_id);
            param[1] = new SqlParameter("@pay_price_sub", pay_price_sub);

            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }


        public static bool ChangeBorrowCashPriceSub(string borrow_id, string cash_price_sub)
        {
            string sp = "Update_borrow_cash_price_sub";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@borrow_id", borrow_id);
            param[1] = new SqlParameter("@cash_price_sub", cash_price_sub);

            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }
        
        
        public static bool ChangeBorrowIsAllPayBack(string borrow_id, bool is_allpayback)
        {
            string sp = "Update_borrow_is_allpayback";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@borrow_id", borrow_id);
            param[1] = new SqlParameter("@is_allpayback", is_allpayback);

            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }

        public static string GetPriceNotPayBack(string borrow_id)
        {
            string sp = "Select_borrow_info";
            SqlParameter param = new SqlParameter("@borrow_id", borrow_id);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            int price_not_payback = 
                (int)ds.Tables[0].Rows[0]["borrow_price"] 
                - (int)ds.Tables[0].Rows[0]["pay_price"]
                - (int)ds.Tables[0].Rows[0]["cash_price"];
            return price_not_payback.ToString();
        }

        public static string GetBudgetIdOfBorrowInfo(string borrow_id)
        {
            string sp = "Select_borrow_info";
            SqlParameter param = new SqlParameter("@borrow_id", borrow_id);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                         sp, param);
            return ds.Tables[0].Rows[0]["budget_id"].ToString();
        }

        public static bool ChangeBudgetBalance(string budget_id, string budget_balance)
        {
            string sp = "Update_budget_balance";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@budget_id", budget_id);
            param[1] = new SqlParameter("@budget_balance", budget_balance);

            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }

        public static bool ChangeBudgetBalanceSub(string budget_id, int budget_balance_sub)
        {
            string sp = "Update_budget_balance_sub";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@budget_id", budget_id);
            param[1] = new SqlParameter("@budget_balance_sub", budget_balance_sub);

            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }

        public static bool ChangePayState(string pay_log_id, string pay_state_id)
        {
            string sp ="Update_pay_state";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@pay_log_id", pay_log_id);
            param[1] = new SqlParameter("@pay_state_id", pay_state_id);
            try
            {
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                        sp, param);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool InsertPayLog(string id, string pay_price, string user_id,string pay_remark,
            bool is_borrow_pay, string[,] pay_item_info)
        {
            string pay_log_id;
            string sp = "Insert_pay_log";
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@pay_log_id", null);
            if (is_borrow_pay)
            {
                param[1] = new SqlParameter("@budget_id", null);
                param[2] = new SqlParameter("@borrow_id", id);
            }
            else
            {
                param[1] = new SqlParameter("@budget_id", id);
                param[2] = new SqlParameter("@borrow_id", null);
            }
            param[3] = new SqlParameter("@pay_price", pay_price);
            param[4] = new SqlParameter("@user_id", user_id);
            param[5] = new SqlParameter("@pay_datetime", DateTime.Now);
            param[6] = new SqlParameter("@pay_remark", pay_remark);
            param[7] = new SqlParameter("@is_borrow_pay", is_borrow_pay);
            param[8] = new SqlParameter("@pay_state_id", "1");
            try
            {
                pay_log_id = SqlHelper.ExecuteScalar(DBC.GetConnString(), CommandType.StoredProcedure, sp, param).ToString();
            }
            catch (Exception ex)
            {
                return false;
            }

            try
            {
                DBAccess.InsertPayItemInfo(pay_log_id, pay_item_info);
            }
            catch(Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool InsertPayItemInfo(string pay_log_id, string[,] pay_item_info)
        {
            string sp = "Insert_pay_item_info";
            for (int i = 0; i < pay_item_info.GetLongLength(0); i++)
            {
                SqlParameter[] param = new SqlParameter[6];
                param[0] = new SqlParameter("@pay_log_id", pay_log_id);
                param[1] = new SqlParameter("@budget_item_info_id", pay_item_info[i, 0]);
                param[2] = new SqlParameter("@pay_item_count", pay_item_info[i, 1]);
                param[3] = new SqlParameter("@pay_item_price", pay_item_info[i, 2]);
                param[4] = new SqlParameter("@pay_item_remark", pay_item_info[i, 3]);
                param[5] = new SqlParameter("@pay_item_info_id", null);
                SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure, sp, param);
            }
            return true;
       
        }

        public static DataSet GetPayPriceLog()
        {
            string sp = "Select_View_pay_pricelogs_All";
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure, sp);
        }

        public static DataSet GetPayLogByState(string pay_state_id)
        {
            string sp = "Select_pay_logs_By_pay_state_id";
            SqlParameter param = new SqlParameter("@pay_state_id", pay_state_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure, 
                sp, param);
        }

        public static DataSet GetPayLog(string pay_state_id, bool is_borrow_pay)
        {
            string sp = "Select_pay_logs_By_is_borrow_pay";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@pay_state_id", pay_state_id);
            param[1] = new SqlParameter("@is_borrow_pay", is_borrow_pay);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetPayFullLog(string pay_state_id)
        {
            string sp = "Select_View_pay_fulllog_by_pay_state_id";
            SqlParameter param = new SqlParameter("@pay_state_id", pay_state_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetBorrowPayFullLog(string pay_state_id)
        {
            string sp = "Select_View_borrow_pay_fulllog_by_pay_state_id";
            SqlParameter param = new SqlParameter("@pay_state_id", pay_state_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }



        public static DataSet GetPayLogById(string pay_log_id)
        {
            string sp = "Select_pay_log";
            SqlParameter param = new SqlParameter("@pay_log_id", pay_log_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetPayLogFullInfo(string pay_log_id)
        {
            string sp = " Select_View_pay_fulllog_by_pay_log_id";
            SqlParameter param = new SqlParameter("@pay_log_id", pay_log_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetPayItemFullInfo(string pay_log_id)
        {
            string sp = "Select_View_pay_item_fullinfo";
            SqlParameter param = new SqlParameter("@pay_log_id", pay_log_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }
        #endregion

        #region BudgetBorrow
        public static string GetBudgetBalanceSum(string budget_range_id, string deparment_id, string budget_type_id)
        {
            string sp = "Select_View_budget_balanceinfo_Dynamic";
            string wherecondition = "budget_range_id = " + budget_range_id +
                " and (department_id = " + deparment_id +
                " or department_pid = " + deparment_id + " ) " +
                " and (budget_type_id = " + budget_type_id +
                " or budget_type_pid = " + budget_type_id + ")" +
                " and is_allapproved = 1";
            SqlParameter param = new SqlParameter("@WhereCondition", wherecondition);
            DataSet ds = SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            if (ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0]["budget_balance_sum"].ToString();
            else
                return null;
        }

        public static bool InsertBorrowInfo(string user_id, string borrow_price,
            string budget_id, string borrow_remark)
        {
            string sp = "Insert_borrow_info";
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@user_id", user_id);
            param[1] = new SqlParameter("@borrow_price", borrow_price);
            param[2] = new SqlParameter("@budget_id", budget_id);
            param[3] = new SqlParameter("borrow_remark", borrow_remark);
            param[4] = new SqlParameter("@is_allpayback", false);
            param[5] = new SqlParameter("@borrow_id", null);
            param[6] = new SqlParameter("@borrow_datetime", DateTime.Now);
            param[7] = new SqlParameter("@allpayback_datetime", null);
            param[8] = new SqlParameter("@pay_price", "0");
            param[9] = new SqlParameter("@cash_price", "0");
            SqlHelper.ExecuteNonQuery(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
            return true;
        }

        public static DataSet GetBorrowFullInfo(string user_id, bool is_allpayback)
        {
            string sp = "Select_View_borrow_fullinfo_by_user_id";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@user_id", user_id);
            param[1] = new SqlParameter("@is_allpayback", is_allpayback);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetBorrowFullInfo(bool is_allpayback)
        {
            string sp = "Select_View_borrow_fullinfo_by_is_allpayback";
            SqlParameter param = new SqlParameter("@is_allpayback", is_allpayback);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static DataSet GetBorrowFullInfo(string borrow_id)
        {
            string sp = "Select_View_borrow_fullinfo_by_borrow_id";
            SqlParameter param = new SqlParameter("@borrow_id", borrow_id);
            return SqlHelper.ExecuteDataset(DBC.GetConnString(), CommandType.StoredProcedure,
                sp, param);
        }

        public static string GetBorrowPriceNotPayBack(string borrow_id)
        {
            DataSet ds = GetBorrowFullInfo(borrow_id);
            if (ds != null)
            {
                return ds.Tables[0].Rows[0]["borrow_price_not_payback"].ToString();
            }
            else
                return null;
        }
        #endregion


    }
}
